package cc.excel.jxl;

import java.io.File;
import java.util.Date;

import org.apache.shiro.crypto.hash.Md5Hash;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class readExcelCreatSQL2 {
	public static void main(String[] args) {
		try {
			//创建workbook
			Workbook workbook = Workbook.getWorkbook(new File("my2.xls"));
			//获取第一个工作表sheet
			Sheet sheet = workbook.getSheet(0);
		
			//获取数据
			for (int i = 0; i < sheet.getRows(); i++) {
				StringBuilder sb = new StringBuilder();
				sb.append("insert into Teacher(name,teacherId,password,sex,createTime,picImg,subjectId) values ");
				for (int j = 0; j < sheet.getColumns(); j++) {
					Cell cell = sheet.getCell(j,i);
					String studentId = "";
					if(j == 0){
						sb.append("('" + cell.getContents()+"',");
					}else if(j == 1){
						studentId = cell.getContents();
						sb.append("'" + cell.getContents()+"',");
						String passwordMd5= new Md5Hash("123456",studentId,2).toHex();
						sb.append("'" + passwordMd5+"',");
						 int r = (int)Math.floor(Math.random() * 2) + 1;
						 sb.append("'" + r+"',");
						 sb.append("'" + new Date(System.currentTimeMillis()).toLocaleString()+"',");
						 if(r == 1){
							 sb.append("'" + "/static/img/t-boy.png"+"',");
						 }else if(r == 2){
							 sb.append("'" + "/static/img/t-girl.png"+"',");
						 }
					}else if(j == 2){
						sb.append("'" + cell.getContents()+"');");
					}
					 
				}
				System.out.println(sb);
			}
			workbook.close();
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
